﻿CREATE TABLE [RestartFramework].[ETLJobStage]
(
	[ETLJobStageId]				SMALLINT			NOT NULL
,	[ETLJobStageName]			SYSNAME				NOT NULL
,	[ETLJobId]					SMALLINT			NOT NULL
,	[ETLJobStageOrder]			SMALLINT			NOT NULL
,	[ETLJobStageEnabled]		BIT					NOT NULL
,	[ETLJobStageIsComplete]		BIT					NOT NULL	DEFAULT CONVERT(BIT,0)
,	[ETLJobStageCreatedDate]	DATETIMEOFFSET(7)	NOT NULL	DEFAULT SYSDATETIME()
,	CONSTRAINT [PK_RestartFrameworkETLJobStage] PRIMARY KEY ([ETLJobStageId]) 
,	CONSTRAINT [UK_RestartFrameworkETLJobStage_ETLJobIdETLJobStageName] UNIQUE ([ETLJobId],[ETLJobStageName])
,	CONSTRAINT [FK_RestartFrameworkETLJobStage_REF_RestartFrameworkETLJob] FOREIGN KEY ([ETLJobId]) REFERENCES [RestartFramework].[ETLJob]([ETLJobId])
)

GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'A friendly unique identifier for this ETLJob',
    @level0type = N'SCHEMA',
    @level0name = N'RestartFramework',
    @level1type = N'TABLE',
    @level1name = N'ETLJobStage',
    @level2type = N'COLUMN',
    @level2name = N'ETLJobStageName'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'A link to the ETLJob that this ETLJobStage is a part of',
    @level0type = N'SCHEMA',
    @level0name = N'RestartFramework',
    @level1type = N'TABLE',
    @level1name = N'ETLJobStage',
    @level2type = N'COLUMN',
    @level2name = N'ETLJobId'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'A toggle for switching the ETLJobStage on or off',
    @level0type = N'SCHEMA',
    @level0name = N'RestartFramework',
    @level1type = N'TABLE',
    @level1name = N'ETLJobStage',
    @level2type = N'COLUMN',
    @level2name = N'ETLJobStageEnabled'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Has the ETLJobStage completed in the most recent execution of the ETLJob',
    @level0type = N'SCHEMA',
    @level0name = N'RestartFramework',
    @level1type = N'TABLE',
    @level1name = N'ETLJobStage',
    @level2type = N'COLUMN',
    @level2name = N'ETLJobStageIsComplete'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'ETLJobStages with a lower ETLJobStageOrder will execute first. This number is determined by the dependencies that are declared b',
    @level0type = N'SCHEMA',
    @level0name = N'RestartFramework',
    @level1type = N'TABLE',
    @level1name = N'ETLJobStage',
    @level2type = N'COLUMN',
    @level2name = N'ETLJobStageOrder'